const pool = require('../config/database');

class HealthAbnormalitiesModel {
  /**
   * 创建健康评价异常记录
   * @param {Object} abnormalityData - 健康评价异常数据
   * @returns {Promise<Object>} 创建的健康评价异常记录
   */
  static async create(abnormalityData) {
    const connection = await pool.getConnection();
    
    try {
      await connection.beginTransaction();
      
      const fields = Object.keys(abnormalityData);
      const values = Object.values(abnormalityData);
      const placeholders = fields.map(() => '?').join(', ');
      
      const sql = `
        INSERT INTO jktj_health_abnormalities (${fields.join(', ')})
        VALUES (${placeholders})
      `;
      
      const [result] = await connection.execute(sql, values);
      
      await connection.commit();
      
      return {
        id: result.insertId,
        ...abnormalityData
      };
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }

  /**
   * 批量创建健康评价异常记录
   * @param {Array<Object>} abnormalityList - 健康评价异常数据数组
   * @returns {Promise<Array>} 创建的健康评价异常记录数组
   */
  static async createBatch(abnormalityList) {
    if (!abnormalityList || abnormalityList.length === 0) {
      return [];
    }

    const connection = await pool.getConnection();
    const results = [];
    
    try {
      await connection.beginTransaction();
      
      for (const abnormalityData of abnormalityList) {
        const fields = Object.keys(abnormalityData);
        const values = Object.values(abnormalityData);
        const placeholders = fields.map(() => '?').join(', ');
        
        const sql = `
          INSERT INTO jktj_health_abnormalities (${fields.join(', ')})
          VALUES (${placeholders})
        `;
        
        const [result] = await connection.execute(sql, values);
        
        results.push({
          id: result.insertId,
          ...abnormalityData
        });
      }
      
      await connection.commit();
      return results;
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }

  /**
   * 根据体检ID获取健康评价异常记录列表
   * @param {number} tjId - 体检ID
   * @returns {Promise<Array>} 健康评价异常记录列表
   */
  static async findByTjId(tjId) {
    const sql = 'SELECT * FROM jktj_health_abnormalities WHERE tj_id = ? ORDER BY id';
    const [rows] = await pool.execute(sql, [tjId]);
    return rows;
  }

  /**
   * 根据ID获取健康评价异常记录
   * @param {number} id - 健康评价异常记录ID
   * @returns {Promise<Object>} 健康评价异常记录
   */
  static async findById(id) {
    const sql = 'SELECT * FROM jktj_health_abnormalities WHERE id = ?';
    const [rows] = await pool.execute(sql, [id]);
    return rows[0] || null;
  }

  /**
   * 更新健康评价异常记录
   * @param {number} id - 健康评价异常记录ID
   * @param {Object} updateData - 更新数据
   * @returns {Promise<boolean>} 更新结果
   */
  static async update(id, updateData) {
    const fields = Object.keys(updateData);
    const values = Object.values(updateData);
    const setClause = fields.map(field => `${field} = ?`).join(', ');
    
    const sql = `UPDATE jktj_health_abnormalities SET ${setClause} WHERE id = ?`;
    const [result] = await pool.execute(sql, [...values, id]);
    
    return result.affectedRows > 0;
  }

  /**
   * 删除健康评价异常记录
   * @param {number} id - 健康评价异常记录ID
   * @returns {Promise<boolean>} 删除结果
   */
  static async delete(id) {
    const sql = 'DELETE FROM jktj_health_abnormalities WHERE id = ?';
    const [result] = await pool.execute(sql, [id]);
    return result.affectedRows > 0;
  }

  /**
   * 根据体检ID删除所有健康评价异常记录
   * @param {number} tjId - 体检ID
   * @returns {Promise<boolean>} 删除结果
   */
  static async deleteByTjId(tjId) {
    const sql = 'DELETE FROM jktj_health_abnormalities WHERE tj_id = ?';
    const [result] = await pool.execute(sql, [tjId]);
    return result.affectedRows > 0;
  }

  /**
   * 批量获取多个体检的健康评价异常记录
   * @param {Array<number>} tjIds - 体检ID数组
   * @returns {Promise<Array>} 健康评价异常记录数组
   */
  static async findByTjIds(tjIds) {
    if (!tjIds || tjIds.length === 0) {
      return [];
    }
    
    const placeholders = tjIds.map(() => '?').join(', ');
    const sql = `
      SELECT * FROM jktj_health_abnormalities 
      WHERE tj_id IN (${placeholders}) 
      ORDER BY tj_id, id
    `;
    const [rows] = await pool.execute(sql, tjIds);
    return rows;
  }

  /**
   * 根据异常类型获取记录
   * @param {number} tjId - 体检ID
   * @param {string} abnormalityType - 异常类型
   * @returns {Promise<Array>} 健康评价异常记录列表
   */
  static async findByType(tjId, abnormalityType) {
    const sql = `
      SELECT * FROM jktj_health_abnormalities 
      WHERE tj_id = ? AND ycfl = ?
      ORDER BY id
    `;
    const [rows] = await pool.execute(sql, [tjId, abnormalityType]);
    return rows;
  }

  /**
   * 获取健康评价异常统计
   * @param {number} tjId - 体检ID
   * @returns {Promise<Object>} 异常统计
   */
  static async getAbnormalityStats(tjId) {
    const sql = `
      SELECT 
        ycfl,
        COUNT(*) as count
      FROM jktj_health_abnormalities 
      WHERE tj_id = ?
      GROUP BY ycfl
    `;
    const [rows] = await pool.execute(sql, [tjId]);
    
    const totalSql = 'SELECT COUNT(*) as total FROM jktj_health_abnormalities WHERE tj_id = ?';
    const [totalRows] = await pool.execute(totalSql, [tjId]);
    
    const stats = {
      total: totalRows[0].total,
      byType: {}
    };
    
    rows.forEach(row => {
      stats.byType[row.ycfl] = row.count;
    });
    
    return stats;
  }

  /**
   * 获取异常分类字典
   * @returns {Object} 异常分类字典
   */
  static getAbnormalityTypeDict() {
    return {
      '1': '脑血管疾病',
      '2': '肾脏疾病',
      '3': '心脏疾病',
      '4': '血管疾病',
      '5': '眼部疾病',
      '6': '神经系统疾病',
      '7': '其他系统疾病'
    };
  }

  /**
   * 获取脑血管疾病字典
   * @returns {Object} 脑血管疾病字典
   */
  static getCerebrovascularDict() {
    return {
      '1': '未发现',
      '2': '缺血性卒中',
      '3': '脑出血',
      '4': '蛛网膜下腔出血',
      '5': '短暂性脑缺血发作',
      '6': '其他'
    };
  }

  /**
   * 获取肾脏疾病字典
   * @returns {Object} 肾脏疾病字典
   */
  static getKidneyDiseaseDict() {
    return {
      '1': '未发现',
      '2': '糖尿病肾病',
      '3': '高血压肾病',
      '4': '慢性肾炎',
      '5': '急性肾炎',
      '6': '其他'
    };
  }

  /**
   * 获取心脏疾病字典
   * @returns {Object} 心脏疾病字典
   */
  static getHeartDiseaseDict() {
    return {
      '1': '未发现',
      '2': '心肌梗死',
      '3': '心绞痛',
      '4': '冠状动脉血运重建',
      '5': '充血性心力衰竭',
      '6': '心前区疼痛',
      '7': '其他'
    };
  }

  /**
   * 获取血管疾病字典
   * @returns {Object} 血管疾病字典
   */
  static getVascularDiseaseDict() {
    return {
      '1': '未发现',
      '2': '动脉闭塞性疾病',
      '3': '下肢血管病变',
      '4': '其他'
    };
  }

  /**
   * 获取眼部疾病字典
   * @returns {Object} 眼部疾病字典
   */
  static getEyeDiseaseDict() {
    return {
      '1': '未发现',
      '2': '视网膜出血或渗出',
      '3': '视乳头水肿',
      '4': '白内障',
      '5': '其他'
    };
  }

  /**
   * 获取神经系统疾病字典
   * @returns {Object} 神经系统疾病字典
   */
  static getNeurologicalDict() {
    return {
      '1': '未发现',
      '2': '有上述异常',
      '3': '其他'
    };
  }

  /**
   * 获取其他系统疾病字典
   * @returns {Object} 其他系统疾病字典
   */
  static getOtherSystemDict() {
    return {
      '1': '未发现',
      '2': '有异常',
      '3': '其他'
    };
  }
}

module.exports = HealthAbnormalitiesModel;